using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.Collections;
using DTO;

namespace DAO
{
    public class CuonSachDao
    {
        #region Methods
        public DataSet LayBang()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From CUON_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);   // can thiet
            da.Fill(ds);
            //B5: Dong ket noi CSDL
            cn.Close();
            return ds;
        }



        public IList LayDanhSach()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From CUON_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            CuonSachDto cs = null;
            ArrayList ds = new ArrayList();
            while (dr.Read())
            {
                cs = new CuonSachDto();
                cs.MaCuonSach = (int)dr["MaCuonSach"];
                cs.MaDauSach = (int)dr["MaDauSach"];
                cs.TinhTrang = (bool)dr["TinhTrang"];

                ds.Add(cs);
            }

            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return ds;
        }



        public void CapNhatBang(DataSet ds)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From CUON_SACH";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);  // khi cap nhat bang phai co dong lenh nay
            da.Update(ds.Tables[0]);
            //B5: Dong ket noi CSDL
            cn.Close();
        }



        public CuonSachDto TimKiem(int maCuonSach, int maDauSach)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From CUON_SACH Where MaCuonSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaCuonSach", OleDbType.Integer);
            cmd.Parameters["@MaCuonSach"].Value = maCuonSach;
            OleDbDataReader dr = cmd.ExecuteReader();
            CuonSachDto cs = null;
            while (dr.Read())
            {
                cs = new CuonSachDto();
                cs.MaCuonSach = (int)dr["MaCuonSach"];
                cs.MaDauSach = (int)dr["MaDauSach"];
                cs.TinhTrang = (bool)dr["TinhTrang"];
            }

            //B5: Dong ket noi CSDL
            cn.Close();
            return cs;
        }



        public void Them(CuonSachDto cs)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into CUON_SACH(MaDauSach, TinhTrang) values(?, ?)";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDauSach", OleDbType.Integer);
            cmd.Parameters.Add("@TinhTrang", OleDbType.Boolean);
            cmd.Parameters["@MaDauSach"].Value = cs.MaDauSach;
            cmd.Parameters["@TinhTrang"].Value = cs.TinhTrang;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            cs.MaCuonSach = (int)cmd.ExecuteScalar();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Xoa(int maCuonSach)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Delete From CUON_SACH Where MaCuonSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaCuonSach", OleDbType.Integer);
            cmd.Parameters["@MaCuonSach"].Value = maCuonSach;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Sua(CuonSachDto cs)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Update CUON_SACH set MaDauSach = ?, TinhTrang = ? where MaCuonSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDauSach", OleDbType.Integer);
            cmd.Parameters.Add("@TinhTrang", OleDbType.Boolean);
            cmd.Parameters.Add("@MaCuonSach", OleDbType.Integer);

            cmd.Parameters["@MaDauSach"].Value = cs.MaDauSach;
            cmd.Parameters["@TinhTrang"].Value = cs.TinhTrang;
            cmd.Parameters["@MaCuonSach"].Value = cs.MaCuonSach;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }



        public void XoaDuaVaoMaDauSach(int maDauSach)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Delete From CUON_SACH Where MaDauSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDauSach", OleDbType.Integer);
            cmd.Parameters["@MaDauSach"].Value = maDauSach;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }

        public bool BanSach(int maDauSach)
        {
            bool flag = false;
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From CUON_SACH Where MaDauSach = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDauSach", OleDbType.Integer);
            cmd.Parameters["@MaDauSach"].Value = maDauSach;
            OleDbDataReader dr = cmd.ExecuteReader();
            CuonSachDto cs = null;
            while (dr.Read())
            {
                cs = new CuonSachDto();
                cs.MaCuonSach = (int)dr["MaCuonSach"];
                cs.MaDauSach = (int)dr["MaDauSach"];
                cs.TinhTrang = (bool)dr["TinhTrang"];

                if (cs.TinhTrang == true) // chua ban
                {
                    flag = true;
                    break;
                }
            }

            //B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();

            if (flag == true)
            {
                CuonSachDao csDao = new CuonSachDao();
                cs.TinhTrang = false;  // da ban
                csDao.Sua(cs);
            }

            return flag;
        }

        #endregion Methods

    }
}
